package com.shop.dao.book;

import com.mysql.jdbc.StringUtils;
import com.shop.dao.BaseDao;
import com.shop.pajo.Book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDaoImpl implements IBookDao{
    @Override
    public List<Book> query(Connection connection, String queryName, String queryCode) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Book> bookList = new ArrayList<Book>();
        Book book = null;
        if(connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select * from book where 1=1 ");
            List<Object> list = new ArrayList<Object>();
            if(!StringUtils.isNullOrEmpty(queryName)){
                sql.append("and bname like ?");
                list.add("%" + queryName + "%");
            }
            if(!StringUtils.isNullOrEmpty(queryCode)){
                sql.append("and bid like ?");
                list.add("%" + queryCode + "%");
            }
            Object[] params = list.toArray();
            resultSet = BaseDao.execute(connection,preparedStatement,resultSet,sql.toString(),params);
            while (resultSet.next()){
                book = new Book();
                book.setId(resultSet.getString("bid"));
                book.setName(resultSet.getString("bname"));
                book.setAuthor(resultSet.getString("bauthor"));
                book.setPress(resultSet.getString("bpress"));
                book.setPicture(resultSet.getString("bpicture"));
                book.setPrice(resultSet.getBigDecimal("bprice"));
                book.setQuantity(resultSet.getBigDecimal("bquantity"));
                bookList.add(book);
            }
            BaseDao.closeResource(null, preparedStatement, resultSet);
        }
        return bookList;
    }

    @Override
    public int add(Connection connection, Book book) throws SQLException {
        PreparedStatement preparedStatement = null;
        int addNum = 0;
        if (connection != null) {
            String sql = "insert into book " +
                    "values(?,?,?,?,?,?,?)";
            Object[] params = {book.getId(), book.getName(), book.getAuthor(),
                    book.getPress(), book.getPicture(), book.getPrice(), book.getQuantity()};
            addNum = BaseDao.execute(connection, preparedStatement, sql, params);
            BaseDao.closeResource(null, preparedStatement, null);
        }
        return addNum;
    }

    @Override
    public Book getBookById(Connection connection, String id) throws SQLException {
        PreparedStatement preparedStatement = null;
        Book book = null;
        ResultSet resultSet = null;
        if(connection != null){
            String sql = "select * from book where bid = ?";
            Object[] params = {id};
            resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql, params);

            while (resultSet.next()){
                book = new Book();
                book.setId(resultSet.getString("bid"));
                book.setName(resultSet.getString("bname"));
                book.setAuthor(resultSet.getString("bauthor"));
                book.setPress(resultSet.getString("bpress"));
                book.setPicture(resultSet.getString("bpicture"));
                book.setPrice(resultSet.getBigDecimal("bprice"));
                book.setQuantity(resultSet.getBigDecimal("bquantity"));
            }
            BaseDao.closeResource(null,preparedStatement,resultSet);
        }
        return book;
    }

    @Override
    public int updateBook(Connection connection, Book book) throws SQLException {
        PreparedStatement preparedStatement = null;
        int bookNum = 0;

        if(connection != null){
            String sql = "update book set bname=?,bauthor=?,bpress=?,bpicture=?,bprice=?,bquantity=? where bid = ?";
            Object[] params = {book.getName(), book.getAuthor(),
                    book.getPress(), book.getPicture(), book.getPrice(), book.getQuantity(), book.getId()};
            bookNum = BaseDao.execute(connection, preparedStatement, sql, params);

            BaseDao.closeResource(null,preparedStatement,null);
        }

        return bookNum;
    }

    @Override
    public int deleteBookById(Connection connection, String id) throws SQLException {
        PreparedStatement preparedStatement = null;
        int bookNum = 0;
        if(connection != null){
            String sql = "delete from book where bid = ?";
            Object[] params = {id};
            bookNum = BaseDao.execute(connection,preparedStatement,sql,params);

            BaseDao.closeResource(null,preparedStatement,null);
        }

        return bookNum;
    }

    @Override
    public List<Book> queryByClass(Connection connection, String bClass) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Book> bookList = new ArrayList<>();
        Book book = null;
        if(connection != null){
            String sql = "select * from book where bid in (select bid from bookclass where class=?)";
            Object[] params = {bClass};
            resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql, params);

            while ((resultSet.next())){
                book = new Book();
                book.setId(resultSet.getString("bid"));
                book.setName(resultSet.getString("bname"));
                book.setAuthor(resultSet.getString("bauthor"));
                book.setPrice(resultSet.getBigDecimal("bprice"));
                book.setPicture(resultSet.getString("bpicture"));
                bookList.add(book);
            }
            BaseDao.closeResource(null,preparedStatement,resultSet);
        }
        return bookList;
    }
}
